package com.lexing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import com.lexing.bean.BaseCondition;
import com.lexing.bean.Enviroment;
import com.lexing.bean.OuterConditions;
import com.lexing.bean.Product;
import com.lexing.bean.ProductFilter;
import com.lexing.bean.ProductType;

public class SqliteHelper {
	private Connection mConnection;
	private static SqliteHelper mInstance;

	private SqliteHelper(String dbPath) {
		int flag = 0;
		try {
			Class.forName("org.sqlite.JDBC");
			mConnection = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
			// mConnection.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			flag = 1;
			e.printStackTrace();
		} catch (SQLException e) {
			flag = 1;
			e.printStackTrace();
		} finally {
			if (flag == 1 && mConnection != null) {
				try {
					mConnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	};

	public static SqliteHelper getInstance(String dbPath) {
		if (mInstance == null) {
			synchronized (SqliteHelper.class) {
				if (mInstance == null) {
					mInstance = new SqliteHelper(dbPath);
				}
			}
		}
		return mInstance;
	}

	public void close() throws SQLException {
		if (!mConnection.isClosed()) {
			mConnection.close();
		}
	}

	public void insertProductType(List<ProductType> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO PRODUCT_TYPE(_id,NAME,IMAGE_PATH) ");
			for (ProductType productType : list) {
				sb.append("SELECT " + productType.getId() + ",'" + productType.getName() + "','"
						+ productType.getImagePath() + "' UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}

	}

	public void insertEnviroment(List<Enviroment> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO ENVIRONMENT(_id,NAME,DESCRIPTION,ICON_PATH,IMAGE_PATH) ");
			for (Enviroment enviroment : list) {
				sb.append("SELECT " + enviroment.getId() + ",'" + enviroment.getName() + "','"
						+ enviroment.getDescription() + "','" + enviroment.getIcon() + "','" + enviroment.getImagePath()
						+ "' UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}
	}

	public void insertOuterCondition(List<BaseCondition> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO OUTER_CONDITIONS(_id,NAME,DESCRIPTION,IMAGE_PATH) ");
			for (BaseCondition condition : list) {
				sb.append("SELECT " + condition.getId() + ",'" + condition.getName() + "','"
						+ condition.getDescription() + "','" + condition.getImagePath() + "' UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}

	}

	public void insertInnerCondition(List<BaseCondition> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO INNER_CONDITIONS(_id,NAME,DESCRIPTION,IMAGE_PATH) ");
			for (BaseCondition condition : list) {
				sb.append("SELECT " + condition.getId() + ",'" + condition.getName() + "','"
						+ condition.getDescription() + "','" + condition.getImagePath() + "' UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}
	}

	public void insertProducts(List<Product> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append(
					"INSERT INTO PRODUCT_DETAILS(PRODUCT_ID,NAME,SHORT_DESC,DESCRIPTION1,DESCRIPTION2,TYPE_ID,IMAGE_PATH) ");
			for (Product product : list) {
				sb.append("SELECT " + product.getProductId() + ",'" + product.getName() + "','" + product.getShortDesc()
						+ "','" + product.getDescription1() + "','" + product.getDescription2() + "',"
						+ product.getProductType() + ",'" + product.getImagePath() + "' UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}

	}

	public void updateOuterConditions(long conditionId, long enviromentId) throws SQLException {
		Statement statement = mConnection.createStatement();
		statement.execute(
				"UPDATE OUTER_CONDITIONS SET ENVIRONMENT_ID = " + enviromentId + " WHERE _id = " + conditionId);
	}

	public void updateInnerConditions(long conditionId, long outerConditionId) throws SQLException {
		Statement statement = mConnection.createStatement();
		statement.execute("UPDATE INNER_CONDITIONS SET OUTER_CONDITIONS_ID = " + outerConditionId + " WHERE _id = "
				+ conditionId);
	}

	public void insertFilters(List<ProductFilter> list) throws SQLException {
		if (list.size() > 0) {
			Statement statement = mConnection.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO PRODUCT_FILTER(PRODUCT_ID,INNER_CONDITIONS_ID) ");
			for (ProductFilter filter : list) {
				sb.append("SELECT " + filter.getProductId() + "," + filter.getInnerConditionsId() + " UNION ALL ");
			}
			sb.delete(sb.length() - 11, sb.length());
			statement.execute(sb.toString());
		}
	}
}
